package com.event.usc.oracleJDBC;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import com.event.usc.dto.*;

import oracle.jdbc.OracleConnection;

/**
 * @author Minghao Yan
 *
 */
public class DatabaseHandler {
	private static final String HOST = "128.125.163.168";
	private static final String PORT = "1521";
	private static final String USERNAME = "team15";   
	private static final String PASSWORD = "scevt";
	private static final String DBNAME = "csci585";
	private static final String URL = "jdbc:oracle:thin:@";
	
	protected OracleConnection connection;

	public DatabaseHandler() {
		String url = URL + HOST + ":" + PORT + ":" + DBNAME;;
		try {
			DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
			connection = (OracleConnection) DriverManager.getConnection(url,
					USERNAME, PASSWORD);
			connection.setAutoCommit(false); // set auto commit false in order to do transaction
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public void closeConnection() {
		if (this.connection != null) {
			try {
				this.connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public boolean isConnected() {
		return this.connection == null;
	}

	public List<EventAbb> getTimeEventAbbs(int low, int high, long start, long end) {
		if (this.connection == null) {
			return null;
		}
		String sqlStmt;
		if (end<0)
			sqlStmt = "select * from (select result.*,rownum rn from (select e.event_id,e.event_title,e.event_start,e.event_end,e.event_category,l.location_code,l.location_geo.SDO_POINT.X,l.location_geo.SDO_POINT.Y from events e,locations l where e.event_location=l.location_code and e.event_end>=" + start + " order by e.event_start asc) result where rownum<=" + high + ") where rn>=" + low;
		else
			sqlStmt = "select * from (select result.*,rownum rn from (select e.event_id,e.event_title,e.event_start,e.event_end,e.event_category,l.location_code,l.location_geo.SDO_POINT.X,l.location_geo.SDO_POINT.Y from events e,locations l where e.event_location=l.location_code and e.event_end>=" + start + " and e.event_start<=" + end + " order by e.event_start asc) result where rownum<=" + high + ") where rn>=" + low;
		return getEventsAbbTemplate(sqlStmt);
	}
	
	public List<EventAbb> getCatEventAbbs(int low, int high, long start, long end, String[] cat)
	{
		if (this.connection == null) {
			return null;
		}
		String sqlStmt;
		String catquery ="";
		for (String s:cat)
			catquery = catquery + "e.event_category='" + s + "' or ";
		catquery = catquery.substring(0, catquery.length()-3);
		if (end<0)
			sqlStmt = "select * from (select result.*,rownum rn from (select e.event_id,e.event_title,e.event_start,e.event_end,l.location_code,l.location_geo.SDO_POINT.X,l.location_geo.SDO_POINT.Y from events e,locations l where e.event_location=l.location_code and (" + catquery + ") and e.event_end>=" + start + " order by e.event_start asc) result where rownum<=" + high + ") where rn>=" + low;
		else
			sqlStmt = "select * from (select result.*,rownum rn from (select e.event_id,e.event_title,e.event_start,e.event_end,l.location_code,l.location_geo.SDO_POINT.X,l.location_geo.SDO_POINT.Y from events e,locations l where e.event_location=l.location_code and (" + catquery + ") and e.event_end>=" + start + " and e.event_start<=" + end + " order by e.event_start asc) result where rownum<=" + high + ") where rn>=" + low;
		return getEventsAbbTemplate(sqlStmt);		
	}
	
	public List<EventAbb> getEventsAbbTemplate(String sqlStmt)
	{
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			ResultSet rs = pstmt.executeQuery();
			List<EventAbb> ret = new LinkedList<EventAbb>();
			while (rs != null && rs.next()) {
				long id = rs.getLong("EVENT_ID");
				String title = rs.getString("EVENT_TITLE");
				String location = rs.getString("LOCATION_CODE");
				long estart = rs.getLong("EVENT_START");
				long eend = rs.getLong("EVENT_END");
				double lat = rs.getDouble("LOCATION_GEO.SDO_POINT.Y");
				double lng = rs.getDouble("LOCATION_GEO.SDO_POINT.X");
				EventAbb event = new EventAbb();
				event.setTitle(title);
				event.setId(id);
				event.setLocation(location);
				event.setStart(EventDT.dateString(estart));
				event.setEnd(EventDT.dateString(eend));
				event.setLat(lat);
				event.setLng(lng);
				ret.add(event);
			}
			pstmt.close();
			return ret;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return null;
		}
	}

	public EventDT getEventDT(long id) {

		if (this.connection == null) {
			return null;
		}
		String sqlStmt = "select e.event_id,e.event_title,e.event_summary,e.event_image,e.event_category,e.event_start,e.event_end,l.location_code,l.location_name,l.location_address,l.location_geo.SDO_POINT.X,l.location_geo.SDO_POINT.Y from events e,locations l where e.event_location=l.location_code and e.event_id=" + id;
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null && rs.next()) {
				long eid = rs.getLong("EVENT_ID");
				String title = rs.getString("EVENT_TITLE");
				String summary = rs.getString("EVENT_SUMMARY");
				String image = rs.getString("EVENT_IMAGE");
				String category = rs.getString("EVENT_CATEGORY");
				String buildingcode = rs.getString("LOCATION_CODE");
				String buildingname = rs.getString("LOCATION_NAME");
				String address = rs.getString("LOCATION_ADDRESS");
				long estart = rs.getLong("EVENT_START");
				long eend = rs.getLong("EVENT_END");
				double lat = rs.getDouble("LOCATION_GEO.SDO_POINT.Y");
				double lng = rs.getDouble("LOCATION_GEO.SDO_POINT.X");
				EventDT event = new EventDT();
				event.setTitle(title);
				event.setId(eid);
				event.setSummary(summary);
				event.setImage(image);
				event.setCategory(category);
				event.setBuildingCode(buildingcode);
				event.setBuildingName(buildingname);
				event.setAddress(address);
				event.setStart(EventDT.dateString(estart));
				event.setEnd(EventDT.dateString(eend));
				event.setLat(lat);
				event.setLng(lng);
				pstmt.close();
				return event;
			}
			else
			{
				pstmt.close();
				return null;
			}
		}catch (SQLException ex) {
			ex.printStackTrace();
			return null;
		}
	}

	public List<UserEvent> getAllUserEvent(long eventid) {
		if (this.connection == null) {
			return null;
		}
		String sqlStmt = "select u.user_id,event_id,checkin,user_comment,firstname,lastname from user_event ue, users u where ue.user_id=u.user_id and ue.checkout is null and ue.event_id=" + eventid;
		return getUserEventTemplate(sqlStmt);
	}

	private List<UserEvent> getUserEventTemplate(String sqlStmt) {
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			ResultSet rs = pstmt.executeQuery();
			List<UserEvent> ret = new LinkedList<UserEvent>();
			while (rs != null && rs.next()) {
				String userid = rs.getString("USER_ID");
				long checkin = rs.getLong("CHECKIN");
				String firstname = rs.getString("FIRSTNAME");
				String lastname = rs.getString("LASTNAME");
				String comment = rs.getString("USER_COMMENT");
				long eventid = rs.getLong("EVENT_ID");
				UserEvent userevent = new UserEvent();
				userevent.setUserid(userid);
				userevent.setFirstname(firstname);
				userevent.setLastname(lastname);
				userevent.setEventid(eventid);
				userevent.setCheckin(checkin);
				userevent.setComment(comment);
				ret.add(userevent);
			}
			pstmt.close();
			return ret;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return null;
		}
	}

	public List<UserEvent> getAllFdUserEvent(long eventid, String userid) {
		if (this.connection == null) {
			return null;
		}
		String sqlStmt = "select ue.user_id,event_id,checkin,user_comment,u.firstname,lastname from user_event ue, users u, friends f where f.user_id ='" + userid + "' and u.user_id = f.friend_id and ue.user_id=f.friend_id and ue.checkout is null and ue.event_id=" + eventid;
		return getUserEventTemplate(sqlStmt);
	}

	public List<UserEvent> getAllFdNcUserEvent(long eventid, String userid) {
		if (this.connection == null) {
			return null;
		}
		String sqlStmt = "select u.user_id,u.firstname,u.lastname from users u, friends f where f.user_id ='" + userid + "' and u.user_id = f.friend_id and not exists (select * from user_event ue where ue.user_id=u.user_id and ue.checkout is null and ue.event_id=" + eventid + ")";
		return getUserEventNcTemplate(sqlStmt, eventid);
	}

	private List<UserEvent> getUserEventNcTemplate(String sqlStmt, Long eventid) {
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			ResultSet rs = pstmt.executeQuery();
			List<UserEvent> ret = new LinkedList<UserEvent>();
			while (rs != null && rs.next()) {
				String userid = rs.getString("USER_ID");
				String firstname = rs.getString("FIRSTNAME");
				String lastname = rs.getString("LASTNAME");
				UserEvent userevent = new UserEvent();
				userevent.setUserid(userid);
				userevent.setFirstname(firstname);
				userevent.setLastname(lastname);
				userevent.setEventid(eventid);
				ret.add(userevent);
			}
			pstmt.close();
			return ret;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return null;
		}
	}

	public Boolean insertInvite(long eventid, String userid, String[] friendid) {
		if (this.connection == null) {
			return false;
		}
		String sqlStmt = "insert into invitations values( ?,?,?,invi_id_seq.nextval," + System.currentTimeMillis() + ",1)";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			for (String id:friendid)
			{
				pstmt.setString(1, userid);
				pstmt.setString(2, id);
				pstmt.setLong(3, eventid);
				pstmt.execute();
			}
			pstmt.close();
			this.connection.commit();
			return true;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
	}

	public Boolean insertCheckIn(long eventid, String userid, String comment) {
		if (this.connection == null) {
			return false;
		}
		String sqlStmt = "insert into user_event values('" + userid + "'," + eventid + "," + System.currentTimeMillis() + ",null,'" + comment + "',user_event_id_seq.nextval)";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			pstmt.execute();
			pstmt.close();
			this.connection.commit();
			return true;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
	}

	public boolean insertCheckOut(long eventid, String userid) {
		if (this.connection == null) {
			return false;
		}
		String sqlStmt = "update user_event set checkout=" + System.currentTimeMillis() + " where event_id=" + eventid + " and user_id='" + userid + "'";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			pstmt.execute();
			pstmt.close();
			this.connection.commit();
			return true;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}	
	}

	public int checkUser(String userid) {
		if (this.connection == null) {
			return -1;
		}
		String sqlStmt = "select user_id from users where user_id='" + userid +"'";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			ResultSet rs = pstmt.executeQuery();
			int size = 0;
			if (rs!=null && rs.next())
				size = 1;
			pstmt.close();
			return size;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return -1;
		}	
	}

	public boolean insertUser(String userid, String firstname, String lastname, String password) {
		if (this.connection == null) {
			return false;
		}
		String sqlStmt = "insert into users values('" + userid + "','" + firstname + "','" + lastname + "','" + password + "')";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			pstmt.execute();
			pstmt.close();
			this.connection.commit();
			return true;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
	}

	public User loginUser(String userid, String password) {
		if (this.connection == null) {
			return new User(null,null,null,null);
		}
		String sqlStmt = "select * from users where user_id='" + userid + "'";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			ResultSet rs = pstmt.executeQuery();
			User user;
			if (rs!=null && rs.next())
				user = new User(rs.getString("USER_ID"),rs.getString("FIRSTNAME"), rs.getString("LASTNAME"), rs.getString("PASSWORD"));
			else
				user = new User(null,null,null,null);
			pstmt.close();
			return user;
		}
		catch (SQLException ex) {
			ex.printStackTrace();
			return new User(null,null,null,null);
		}
	}

	public List<EventAbb> getKeyEventAbbs(int low, int high, long start,
			long end, String key) {
		if (this.connection == null) {
			return null;
		}
		String sqlStmt;
		if (end<0)
			sqlStmt = "select * from (select result.*,rownum rn from (select score(1),e.event_id,e.event_title,e.event_start,e.event_end,l.location_code,l.location_geo.SDO_POINT.X,l.location_geo.SDO_POINT.Y from events e,locations l where e.event_location=l.location_code and contains(event_doc, '" + key + "', 1)>0 and e.event_end>=" + start + " order by score(1) desc, event_start asc) result where rownum<=" + high + ") where rn>=" + low;
		else
			sqlStmt = "select * from (select result.*,rownum rn from (select score(1),e.event_id,e.event_title,e.event_start,e.event_end,l.location_code,l.location_geo.SDO_POINT.X,l.location_geo.SDO_POINT.Y from events e,locations l where e.event_location=l.location_code and contains(event_doc, '" + key + "', 1)>0 and e.event_end>=" + start + " and e.event_start<=" + end + " order by score(1) desc, event_start asc) result where rownum<=" + high + ") where rn>=" + low;
		return getEventsAbbTemplate(sqlStmt);	
	}

	public boolean addFriend(String userid, String friendid) {
		if (this.connection == null) {
			return false;
		}
		String sqlStmt = "insert into friends values('" + userid +"','" + friendid + "')";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			pstmt.execute();
			pstmt.close();
			this.connection.commit();
			return true;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
	}

	public List<EventAbb> getFilterEventAbbs(int low, int high, long start,
			long end, String[] cat, String key) {
		if (this.connection == null) {
			return null;
		}
		String sqlStmt;
		String catquery ="";
		for (String s:cat)
			catquery = catquery + "e.event_category='" + s + "' or ";
		catquery = catquery.substring(0, catquery.length()-3);
		if (end<0)
			sqlStmt = "select * from (select result.*,rownum rn from (select score(1),e.event_id,e.event_title,e.event_start,e.event_end,l.location_code,l.location_geo.SDO_POINT.X,l.location_geo.SDO_POINT.Y from events e,locations l where e.event_location=l.location_code and (" + catquery + ") and contains(event_doc, '" + key + "', 1)>0 and e.event_end>=" + start + " order by score(1) desc, event_start asc) result where rownum<=" + high + ") where rn>=" + low;
		else
			sqlStmt = "select * from (select result.*,rownum rn from (select score(1),e.event_id,e.event_title,e.event_start,e.event_end,l.location_code,l.location_geo.SDO_POINT.X,l.location_geo.SDO_POINT.Y from events e,locations l where e.event_location=l.location_code and (" + catquery + ") and contains(event_doc, '" + key + "', 1)>0 and e.event_end>=" + start + " and e.event_start<=" + end + " order by score(1) desc, event_start asc) result where rownum<=" + high + ") where rn>=" + low;
		return getEventsAbbTemplate(sqlStmt);	
	}

	public List<Alert> getAlerts(String userid) {
		if (this.connection == null) {
			return null;
		}
		String sqlStmt = "select i.invitation_id,u.user_id,u.firstname,u.lastname,i.event_id,event_title,i.timestamp from users u,invitations i,events e where i.user_id=u.user_id and i.invitee_id='" + userid + "' and i.active=1 and i.event_id=e.event_id";
		//String updateStmt = "update invitations set active=0 where invitee_id='" + userid + "'";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			ResultSet rs = pstmt.executeQuery();
			List<Alert> result = new LinkedList<Alert>();
			while (rs!=null && rs.next())
			{
				String iid = rs.getString("USER_ID");
				String firstname = rs.getString("FIRSTNAME");
				String lastname = rs.getString("LASTNAME");
				String title = rs.getString("EVENT_TITLE");
				long eventid = rs.getLong("EVENT_ID");
				long alertid = rs.getLong("invitation_id");
				long timestamp = rs.getLong("timestamp");
				Alert alert = new Alert();
				alert.setEventid(eventid);
				alert.setAlertid(alertid);
				alert.setUserid(iid);
				alert.setTimestamp(EventDT.dateString(timestamp));
				alert.setFirstname(firstname);
				alert.setLastname(lastname);
				alert.setTitle(title);
				result.add(alert);
			}
			pstmt.close();
			this.connection.commit();
			return result;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return null;
		}
	}

	public List<EventAbb> getRangeEventAbbs(double lat, double lng,
			double radius, long current) {
		if (this.connection == null) {
			return null;
		}
		long later = current + 1000*60*60*24;
		String sqlStmt = "select e.event_id,e.event_title,e.event_start,e.event_end,l.location_code,l.location_geo.SDO_POINT.X,l.location_geo.SDO_POINT.Y from events e,locations l "
				+ "where e.event_end>" + current + " and e.event_start<" + later + " and e.event_location=l.location_code and "
				+ "SDO_WITHIN_DISTANCE(l.location_geo, MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE("+lng+", "+lat+", NULL),NULL,NULL), "
				+ "'distance=" + radius + "') = 'TRUE'";
		return getEventsAbbTemplate(sqlStmt);	
	}

	public List<EventAbb> getKNNEventAbbs(double lat, double lng, int k,
			long current) {
		if (this.connection == null) {
			return null;
		}
		long later = current + 1000*60*60*24;
		String sqlStmt = "select * from "
				+ "(select SDO_GEOM.SDO_DISTANCE(l.location_geo, SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(" + lng + ", " + lat + ", null),NULL,NULL), 10) as distance, "
				+ "e.event_id,e.event_title,e.event_start,e.event_end,l.location_code,l.location_geo.SDO_POINT.X,l.location_geo.SDO_POINT.Y "
				+ "from events e,locations l where e.event_end>" + current + " and e.event_start<" + later + " and e.event_location=l.location_code order by distance asc) "
				+ "where rownum<=" + k;
		return getEventsAbbTemplate(sqlStmt);
	}

	public List<UserEvent> getAllFd(String userid) {
		if (this.connection == null) {
			return null;
		}
		String sqlStmt = "select ue1.user_id,checkin,user_comment,ue1.event_id,event_title,firstname,lastname from "
				+ "(select max(checkin) as mcheckin,user_id from "
				+ "(select ue.user_id,ue.checkin from user_event ue where ue.user_id in "
				+ "(select friend_id from friends where user_id='" + userid + "' ) and ue.checkout is null) group by user_id) result,"
				+ "user_event ue1,events e,users u where ue1.checkin=result.mcheckin and ue1.user_id=result.user_id and "
				+ "ue1.checkout is null and ue1.user_id=u.user_id and ue1.event_id=e.event_id";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			ResultSet rs = pstmt.executeQuery();
			List<UserEvent> ret = new LinkedList<UserEvent>();
			while (rs != null && rs.next()) {
				String fuserid = rs.getString("USER_ID");
				long checkin = rs.getLong("CHECKIN");
				String firstname = rs.getString("FIRSTNAME");
				String lastname = rs.getString("LASTNAME");
				String comment = rs.getString("USER_COMMENT");
				long eventid = rs.getLong("EVENT_ID");
				String title = rs.getString("event_title");
				UserEvent userevent = new UserEvent();
				userevent.setUserid(fuserid);
				userevent.setFirstname(firstname);
				userevent.setLastname(lastname);
				userevent.setEventid(eventid);
				userevent.setCheckin(checkin);
				userevent.setComment(comment);
				userevent.setTitle(title);
				ret.add(userevent);
			}
			pstmt.close();
			return ret;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return null;
		}
	}

	public int getCountAlerts(String userid) {
		if (this.connection == null) {
			return -1;
		}
		String sqlStmt = "select count(*) as count from invitations where invitee_id='" + userid + "' and active=1";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			ResultSet rs = pstmt.executeQuery();
			int result = -1;
			if (rs!=null && rs.next())
			 result = rs.getInt("COUNT");
			pstmt.close();
			return result;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return -1;
		}
	}

	public boolean removeAlert(long alertid) {
		if (this.connection == null) {
			return false;
		}
		
		String updateStmt = "update invitations set active=0 where invitation_id=" + alertid;
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(updateStmt);
			pstmt.execute();
			pstmt.close();
			this.connection.commit();
			return true;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
	}

	public long getCurrent(String userid) {
		if (this.connection == null) {
			return -1;
		}
		String sqlStmt = "select * from (select event_id,checkin from user_event where user_id='" + userid + "' and checkout is null order by checkin desc) where rownum<=1";
		try {
			PreparedStatement pstmt = this.connection.prepareStatement(sqlStmt);
			ResultSet rs = pstmt.executeQuery();
			long eventid = -1;
			if (rs!=null && rs.next())
				eventid = rs.getLong("event_id");
			pstmt.close();
			return eventid;
		}catch (SQLException ex) {
			ex.printStackTrace();
			return -1;
		}
	}
}